package com.geline.easyexcel.handler;

import cn.hutool.core.lang.Assert;
import cn.hutool.extra.spring.SpringUtil;
import com.alibaba.excel.write.handler.SheetWriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteWorkbookHolder;
import com.geline.easyexcel.annotation.ExcelDropDown;
import com.geline.easyexcel.domain.DropDownItem;
import com.geline.easyexcel.domain.ParentDict;
import com.geline.easyexcel.manager.DropDownService;
import com.geline.easyexcel.utils.ExcelColNumUtil;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddressList;

import java.lang.reflect.Field;
import java.util.*;

/**
 * 下拉框处理器
 *
 * @author: mengxin
 * @date: 2024/1/8 18:28
 */
@Slf4j
public class ExcelDropDownHandler implements SheetWriteHandler {

    //{{"dictName":"","dictCode":"","dictLevel":"","dictSheet":""}:{"itemList":["男","女"],"formula":"","firstRow":1,"lastRow":10000}}
    private Map<String, DropDownItem> dictMap;

    public ExcelDropDownHandler(Class headClass){
        this.dictMap = this.parseDictMap(headClass);
    }

    public ExcelDropDownHandler(Map<String, DropDownItem> dictMap){
        this.dictMap = dictMap;
    }

    @Override
    public void afterSheetCreate(WriteWorkbookHolder workbookHolder, WriteSheetHolder sheetHolder) {
        Workbook workbook = workbookHolder.getWorkbook();
        // 添加单元格：数据有效性
        DataValidationHelper validationHelper = sheetHolder.getSheet().getDataValidationHelper();

        Integer rowIndex = 0;
        Set<String> workbookNameSet = new HashSet<>();
        for(String head : dictMap.keySet()){
            //设置标题单元格：每一行的数据有效性
            DropDownItem item = dictMap.get(head);

            Sheet sheet = workbook.getSheet(item.getDropDown().dictSheet());
            if(sheet==null){
                // 创建sheet，保存下拉数据源，这里主要是销售人员姓名和销售人员id
                sheet = workbook.createSheet(item.getDropDown().dictSheet());
            }
            //一级、N级下拉
            addWorkbookName(item, sheet, workbook, rowIndex, workbookNameSet);
            ++rowIndex;

            this.addDataValidation(sheetHolder, validationHelper, item);
        }
    }

    private void addDataValidation(WriteSheetHolder sheetHolder, DataValidationHelper helper, DropDownItem item){
        int colIndex = item.getDropDown().index();
        int firstRow = item.getDropDown().firstRow();
        int lastRow = item.getDropDown().lastRow();
        CellRangeAddressList nameRange = new CellRangeAddressList(firstRow, lastRow, colIndex, colIndex);
        String formula = item.getFormula();
        DataValidationConstraint constraint = helper.createFormulaListConstraint(formula); // 数据源的第一列
        log.debug("创建动态下拉框[{}-{}-{}-{}]值{}", item.getDictName(), firstRow, lastRow, colIndex, formula);

        DataValidation validation = helper.createValidation(constraint, nameRange);
        validation.setShowErrorBox(true);
        validation.createErrorBox("友情提示", "请输入下拉选项中的内容");
        sheetHolder.getSheet().addValidationData(validation);
    }

    //添加名称管理器
    private void addWorkbookName(DropDownItem item, Sheet dsSheet, Workbook workbook, Integer dictRow, Set<String> workbookNameSet){
        //创建数据源－行数据
        Row row = dsSheet.createRow(dictRow);
        //第1列为 name
        String dictName = item.getDictName();
        row.createCell(0).setCellValue(dictName);
        //第2列为 code
        String dictCode = item.getDictCode();
        row.createCell(1).setCellValue(dictCode);
        Collection<String> itemList = item.getItemList();
        for(int i=0; i<itemList.size(); i++){
            //后面每列对应一个字典值
            String[] array = itemList.toArray(new String[0]);
            row.createCell(i+2).setCellValue(array[i]);
        }

        /**
         * 名称管理器第一列不能使用中文，会报异常：不支持 (除 数字 _ 字母)；可以使用VLOOKUP() 先取中文对应的英文代码，再从名称管理器中读取下拉序列
         * 报异常提示：Caused by: java.lang.IllegalArgumentException: Invalid name: '有无明显裂缝、倾斜、变形等': name must be letter, digit, period, or underscore
         */
        if(!workbookNameSet.contains(dictCode)){
            workbookNameSet.add(dictCode);
            // 创建名称管理器，方便将来使用函数 indirect()
            Name workbookName = workbook.createName();
            workbookName.setNameName(dictCode);
            String charStr = ExcelColNumUtil.numToColCode(itemList.size() + 2);

            //DS1!$C$2:$\$2
            String formula = String.format("%s!$C$%s:$%s$%s", dsSheet.getSheetName(), dictRow+1, charStr, dictRow+1);
            //log.debug(">>>>> add workbookName {}={}={}", item.getDictName(), dictCode, formula);
            workbookName.setRefersToFormula(formula);
        }
    }

    /**
     * 解析表头类中的下拉注解
     * @param headClass
     * @return Map<下拉框列索引, 下拉框内容> map
     */
    private Map<String, DropDownItem> parseDictMap(Class headClass) {
        Map<String, DropDownItem> dictMap = new HashMap<>();

        List<ExcelDropDown> dropDownList = new ArrayList<>();
        Field[] fields = headClass.getDeclaredFields();
        for (int i = 0; i < fields.length; i++) {
            Field field = fields[i];
            ExcelDropDown dropDown = field.getAnnotation(ExcelDropDown.class);
            if (dropDown != null) {
                String dictCode = dropDown.dictCode();
                Assert.notBlank(dictCode, String.format("%s can not set dictCode !", field.getName()));
                dropDownList.add(dropDown);
            }
        }

        DropDownService service = SpringUtil.getBean(DropDownService.class);
        dropDownList.stream().forEach(row -> {
            List<ParentDict> list = service.getDataObject(row);
            if (list != null) {
                for(ParentDict bean : list){
                    DropDownItem item = new DropDownItem(row, bean);
                    dictMap.put(bean.getDictName(), item);
                }
            }
        });
        return dictMap;
    }
}
